#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "

delete  from hive.dm.sq_dm_cul_day_cnt where 1=1;
insert into hive.dm.sq_dm_cul_day_cnt
with tmp as(
select
    c_day ,
    substring(c_day,1,7) as c_month,   -- 月
    substring(c_day,1,4) as c_year, -- 年
    orgin_type ,
    itcast_school_id ,
    itcast_school_name ,
    itcast_subject_id ,
    itcast_subject_name ,
    tdepart_id ,
    tdepart_name,
    origin_channel ,
    r_num  ,--意向人数
    s_num ,--报名人数
    live_num,--有效人数
    r_cnt ,--意向转化率
    online_cnt ,--有效线上转化率
    group_type
    from hive.edu_dws.sq_dws_cul_day_cnt
),tmp2 as(
select
    c_day,
    c_month,
    c_year,
     -- 时间维度的标记
           case
               when grouping(c_day) = 0 then '日'
               when grouping(c_month) = 0 then '月'
               when grouping(c_year) = 0 then '年'
               else 'other'
               end               as time_type,
    orgin_type,
    itcast_school_id,
    itcast_school_name,
    itcast_subject_id,
    itcast_subject_name,
    tdepart_id,
    tdepart_name,
    origin_channel,
    case
         when grouping (itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name)=0 then '校区学科'
         when grouping (itcast_subject_id,itcast_subject_name)=0 then '学科'
         when grouping (itcast_school_id,itcast_school_name)=0 then'校区'
         when grouping (origin_channel)=0 then '渠道来源'
         when grouping (tdepart_id,tdepart_name)=0 then'咨询中心'
         when grouping (orgin_type)=0 then '数据来源,线上线下'
         else 'all'
    end as group_type,
     sum(r_num)as r_num_total,
     sum(s_num)as s_num_total,
     sum(live_num)as live_num_total,
     (sum(s_num)/sum(r_num))as r_cnt_total
     --(sum(s_num)/sum(live_num))as online_cnt
from tmp
group by
grouping sets (--日期上卷
    (c_day,orgin_type,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name),  -- 每天线上线下各校区各学科报名人数
    (c_day,itcast_school_id,itcast_school_name),  -- 每天各个校区报名人数
    (c_day,orgin_type,itcast_school_id,itcast_school_name), -- 每天线上线下各校区报名人数
    (c_day,orgin_type,itcast_subject_id,itcast_subject_name),    -- 每天线上线下各学科报名人数
    (c_day,orgin_type,origin_channel),-- 每天各来源渠道报名人数
    (c_day,orgin_type,tdepart_id,tdepart_name) , -- 每天线上线下各咨询中心报名人数
    (c_day,orgin_type),---每天线上线下报名人数
    --月份上卷
    (c_month,orgin_type,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name),  -- 每天线上线下各校区各学科报名人数
    (c_month,itcast_school_id,itcast_school_name),  -- 每天各个校区报名人数
    (c_month,orgin_type,itcast_school_id,itcast_school_name), -- 每天线上线下各校区报名人数
    (c_month,orgin_type,itcast_subject_id,itcast_subject_name),    -- 每天线上线下各学科报名人数
    (c_month,orgin_type,origin_channel),-- 每天各来源渠道报名人数
    (c_month,orgin_type,tdepart_id,tdepart_name) , -- 每天线上线下各咨询中心报名人数
    (c_month,orgin_type),---每天线上线下报名人数
    --年份上卷
    (c_year,orgin_type,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name),  -- 每年线上线下各校区各学科报名人数
    (c_year,orgin_type,itcast_school_id,itcast_school_name), -- 每年线上线下各校区报名人数
    (c_year,orgin_type,itcast_subject_id,itcast_subject_name),    -- 每天线上线下各学科报名人数
    (c_year,orgin_type,origin_channel),-- 每年各来源渠道报名人数
    (c_year,orgin_type,tdepart_id,tdepart_name), -- 每年线上线下各咨询中心报名人数
    (c_year,orgin_type)--每年线上线下报名人数
    )   )
select
    c_day,
    c_month,
    c_year,
    orgin_type,
    itcast_school_id,
    itcast_school_name,
    itcast_subject_id,
    itcast_subject_name,
    tdepart_id,
    tdepart_name,
    origin_channel,
    r_num_total,
    s_num_total,
    live_num_total,
    r_cnt_total
from tmp2 where time_type='年' or time_type='月' or time_type='日';"
